# database.py
import sqlite3
import os

from task import Task


class TaskDatabase:
    db_path = './run/yt.db'

    def __init__(self):
        if not os.path.exists('./run'):
            os.mkdir('./run')
        self.conn = sqlite3.connect(self.db_path)
        self._create_tables()

    def _create_tables(self):
        cursor = self.conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                url TEXT UNIQUE NOT NULL,
                title TEXT,
                format TEXT,
                status INTEGER DEFAULT 0,
                percent REAL DEFAULT 0,
                path TEXT,
                is_downloading INTEGER DEFAULT 0,
                is_stop INTEGER DEFAULT 0
            )
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS settings (
                key TEXT PRIMARY KEY,
                value TEXT
            )
        ''')
        self.conn.commit()

    def new_task_list(self, tasks):
        with self.conn:
            cursor = self.conn.cursor()
            for t in tasks:
                cursor.execute("SELECT * FROM Tasks WHERE url = ? and path = ?", (t.url, t.path))
                if not cursor.fetchone():
                    cursor.execute(
                        """
                        INSERT INTO tasks (url, path, title, format)
                        VALUES (?, ?, ?, ?)
                        """,
                        (t.url, t.path, t.title, t.format)
                    )

    def insert_task(self, url, path):
        cursor = self.conn.cursor()
        cursor.execute("SELECT * FROM Tasks WHERE url = ? and path = ?", (url, path))
        ret = cursor.fetchone()
        if not ret:
            cursor.execute("INSERT INTO tasks (url, path) VALUES (?, ?)", (url, path))
            self.conn.commit()
            return True, cursor.lastrowid
        else:
            return False, ret[0]

    def update_title(self, task_id, title):
        curses = self.conn.cursor()
        curses.execute('UPDATE tasks SET title = ? WHERE id = ?', (title, task_id))
        self.conn.commit()

    def get_next_pending_task(self):
        cursor = self.conn.cursor()
        cursor.execute("SELECT* FROM tasks WHERE status=0 AND is_downloading = 0 ORDER BY id ASC LIMIT 1")
        ret = cursor.fetchone()
        if ret:
            t = Task()
            t.id, t.url, t.title, t.format, t.status, t.percent, t.path, t.is_downloading, *_ = ret
            return t
        else:
            return None

    def finish_task(self, task_id):
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE tasks SET status = 1, percent=100 WHERE id=?", (task_id,))
            conn.commit()

    def fail_task(self, task_id):
        cursor = self.conn.cursor()
        cursor.execute("UPDATE tasks SET status=-1 WHERE id=?", (task_id,))
        self.conn.commit()

    def set_setting(self, key, value):
        cursor = self.conn.cursor()
        cursor.execute("REPLACE INTO settings (key, value) VALUES (?, ?)", (key, value))
        self.conn.commit()

    def get_setting(self, key, default):
        cursor = self.conn.cursor()
        cursor.execute("SELECT value FROM settings WHERE key=?", (key,))
        result = cursor.fetchone()
        return result[0] if result else default

    def save_default_save_directory(self, path):
        self.set_setting('path', path)

    def load_default_save_directory(self):
        self.get_setting('path', './download')

    def load_all_tasks(self):
        cursor = self.conn.cursor()
        cursor.execute("SELECT id, url, title, format, status, percent FROM tasks ORDER BY id ASC")
        result = []
        for item in cursor.fetchall():
            t = Task()
            t.id, t.url, t.title, t.format, t.status, t.percent, t.path, t.is_downloading, *_ = item
            result.append(t)
        return result

    def get_downloading_tasks(self):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM tasks WHERE status = 0 AND is_stop = 0')
        result = []
        for item in cursor.fetchall():
            t = Task()
            t.id, t.url, t.title, t.format, t.status, t.percent, t.path, t.is_downloading, t.is_stop = item
            result.append(t)
        return result

    def get_task_by_id(self, task_id):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM tasks WHERE id = ?', (task_id,))
        t = Task()
        t.id, t.url, t.title, t.format, t.status, t.percent, t.path, t.is_downloading, t.is_stop = cursor.fetchone()
        return t

    def update_progress(self, id, percent):
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('UPDATE tasks SET percent =? WHERE id = ?', (percent, id))
            conn.commit()

    def get_downloaded_tasks(self):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM tasks WHERE status = 1 ORDER BY id DESC')
        result = []
        for item in cursor.fetchall():
            t = Task()
            t.id, t.url, t.title, t.format, t.status, t.percent, t.path, t.is_downloading, *_ = item
            result.append(t)
        return result

    def new_task(self, task):
        print('新建任务', task)
        cursor = self.conn.cursor()
        cursor.execute("SELECT * FROM Tasks WHERE url = ?", (task.url,))
        ret = cursor.fetchone()
        if not ret:
            cursor.execute("INSERT INTO tasks (url, path, title, format) VALUES (?, ?, ?, ?)",
                           (task.url, task.path, task.title, task.format))
            self.conn.commit()
            return True, cursor.lastrowid
        else:
            return False, ret[0]

    def set_not_downloading(self):
        cursor = self.conn.cursor()
        cursor.execute('UPDATE tasks SET is_downloading = 0, is_stop = 0 WHERE status =0 ')
        self.conn.commit()

    def set_is_downloading(self, id):
        cursor = self.conn.cursor()
        cursor.execute('UPDATE tasks SET is_downloading = 1, is_stop = 0 WHERE id = ?', (id,))
        self.conn.commit()

    def retry_download(self, id):
        cursor = self.conn.cursor()
        cursor.execute('UPADTE tasks SET is_downloading = 0, status = 0, is_stop = 0 WHERE id = ?', (id,))
        self.conn.commit()

    def stop_task(self, task):
        cursor = self.conn.cursor()
        cursor.execute('UPDATE tasks SET is_stop = 1, is_downloading = 0 WHERE id = ?', (task.id,))
        self.conn.execute()

    def continue_task(self, task):
        cursor = self.conn.cursor()
        cursor.execute('UPDATE tasks SET is_stop = 0, is_downloading = 1 WHERE id = ?', (task.id,))
        self.conn.execute()


if __name__ == '__main__':
    db = TaskDatabase()
    id = db.insert_task('url', 'path')
    print(id)
    ret = db.get_downloading_tasks()
    print(ret)
